package casamento.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import casamento.Bean.EstabelecimentoBean;

public class EstabelecimentoDAO {

	Connection connection;
	Conexao con = new Conexao();

	// inserir estabelecimento
	public void inserir(EstabelecimentoBean estBean) {

		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction t = session.beginTransaction();
		session.save(estBean);
		t.commit();

	}
	
	// inserir observação quando estabelecimento estiver inativo
	public void inserirObs(String isInativo, Integer id) throws ClassNotFoundException, SQLException{
		this.connection = con.getConnection();
		String sql = "UPDATE Tbestabelecimento SET obsInativo=? WHERE idTbEstabelecimento=?" ;
		
		PreparedStatement stmt = connection.prepareStatement(sql);
		stmt.setString(1, isInativo);
		stmt.setInt(2, id);
		stmt.execute();
		stmt.close();
	}
	
	//carregar mensagens
	public String carregarMsgObs(Integer id) throws SQLException, ClassNotFoundException{
		
		this.connection = con.getConnection();
		String sql = "SELECT * FROM tbestabelecimento WHERE idTbEstabelecimento = ?" ;
		String msg = "";
		
		PreparedStatement stmt = connection.prepareStatement(sql);
		stmt.setInt(1, id);
		
		ResultSet rs = stmt.executeQuery();

		while (rs.next()) {
			EstabelecimentoBean est = new EstabelecimentoBean();

			est.setObsInativo(rs.getString("obsInativo"));
			msg = est.getObsInativo();
			
		}

		return msg;
	}
	
	/**
	 * Método responsavél por alterar dados do estabelecimento.
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 * 
	 */
	public void alterarEstabelecimento(String campo, String valor, Integer id) throws ClassNotFoundException, SQLException{
		
		this.connection = con.getConnection();
		String sql = "UPDATE Tbestabelecimento SET " + campo + "=? WHERE idTbEstabelecimento = ?" ;
		
		PreparedStatement stmt = connection.prepareStatement(sql);
		stmt.setString(1, valor);
		stmt.setInt(2, id);
		stmt.execute();
		stmt.close();
	}
	
	/**
	 * Método responsavél por alterar dados do estabelecimento.
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 * 
	 */
	public void alterarEnderecoEstabelecimento(EstabelecimentoBean estEnd) throws ClassNotFoundException, SQLException{
		
		this.connection = con.getConnection();
		String sql = "UPDATE Tbestabelecimento SET cep=?, rua=?, numero=?, bairro=?, cidade=?, uf=? WHERE idTbEstabelecimento = ?" ;
		
		PreparedStatement stmt = connection.prepareStatement(sql);
		stmt.setString(1, estEnd.getCep());
		stmt.setString(2, estEnd.getRua());
		stmt.setInt(3, estEnd.getNumero());
		stmt.setString(4, estEnd.getBairro());
		stmt.setString(5, estEnd.getCidade());
		stmt.setString(6, estEnd.getUf());
		stmt.setInt(7, estEnd.getId());
		
		stmt.execute();
		stmt.close();
	}
	
	/**
	 *Método responsável por desativar estabelecimento.
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 *
	 */
	public void desativarPorId(Integer id, String valor) throws ClassNotFoundException, SQLException{
		this.connection = con.getConnection();
		
		String sql = "UPDATE Tbestabelecimento SET isAtivo = ? WHERE idTbEstabelecimento = ?";
		
		PreparedStatement stmt = connection.prepareStatement(sql);
		stmt.setString(1, valor);
		stmt.setInt(2, id);
		
		stmt.execute();
		stmt.close();
				
	}
	

	// listar Estabelecimentos
	public List<EstabelecimentoBean> listar() throws ClassNotFoundException,
			SQLException {
		this.connection = con.getConnection();

		List<EstabelecimentoBean> lista = new ArrayList<EstabelecimentoBean>();
		String sql = "SELECT * FROM Tbestabelecimento";

		PreparedStatement stmt = connection.prepareStatement(sql);
		ResultSet rs = stmt.executeQuery();

		while (rs.next()) {
			EstabelecimentoBean est = new EstabelecimentoBean();

			est.setId(rs.getInt("idTbEstabelecimento"));
			est.setNome(rs.getString("nome"));
			est.setArea(rs.getString("area"));

			lista.add(est);
		}

		return lista;
	}
	
	// Pesquisar estabelecimento por id Empresa
		public EstabelecimentoBean pequisarPorId(Integer id) {

			Session session = HibernateUtil.getSessionFactory().openSession();
			Transaction t = session.beginTransaction();
			EstabelecimentoBean  estabelecimento = (EstabelecimentoBean) session.get(EstabelecimentoBean.class, id);

			return estabelecimento;

		}
	
	// pesquisar estabelecimentos por Nome
	@SuppressWarnings("unchecked")
	public List<EstabelecimentoBean> pesquisaPorNome(String nome) {

		List<EstabelecimentoBean> listaPorNome = new ArrayList<EstabelecimentoBean>();
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction t = session.beginTransaction();
		Criteria crit = session.createCriteria(EstabelecimentoBean.class);
		crit.add(Restrictions.like("nome", "%" + nome + "%"));
		crit.addOrder(Order.asc("nome"));
		listaPorNome = crit.list();
		t.commit();

		return listaPorNome;

	}

	// Pesquisar estabelecimento por CNPJ
	@SuppressWarnings("unchecked")
	public List<EstabelecimentoBean> pesquisaPorCNPJ(String cnpj) {

		List<EstabelecimentoBean> listaPorCnpj = new ArrayList<EstabelecimentoBean>();
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction t = session.beginTransaction();
		Criteria crit = session.createCriteria(EstabelecimentoBean.class);
		crit.add(Restrictions.like("cnpj", "%" + cnpj + "%"));
		crit.addOrder(Order.asc("nome"));
		listaPorCnpj = crit.list();
		t.commit();

		return listaPorCnpj;

	}

	// Pesquisar estabelecimento por CEP
	@SuppressWarnings("unchecked")
	public List<EstabelecimentoBean> pesquisaPorCEP(String cep) {

		List<EstabelecimentoBean> listaPorCep = new ArrayList<EstabelecimentoBean>();
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction t = session.beginTransaction();
		Criteria crit = session.createCriteria(EstabelecimentoBean.class);
		crit.add(Restrictions.like("cep", "%" + cep + "%"));
		crit.addOrder(Order.asc("nome"));
		listaPorCep = crit.list();
		t.commit();

		return listaPorCep;

	}

	// Pesquisar estabelecimento por Rua
	@SuppressWarnings("unchecked")
	public List<EstabelecimentoBean> pesquisaPorRua(String rua) {

		List<EstabelecimentoBean> listaPorRua = new ArrayList<EstabelecimentoBean>();
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction t = session.beginTransaction();
		Criteria crit = session.createCriteria(EstabelecimentoBean.class);
		crit.add(Restrictions.like("rua", "%" + rua + "%"));
		crit.addOrder(Order.asc("nome"));
		listaPorRua = crit.list();
		t.commit();

		return listaPorRua;

	}

	// Pesquisar estabelecimento por Cidade
	@SuppressWarnings("unchecked")
	public List<EstabelecimentoBean> pesquisaPorCidade(String cidade) {

		List<EstabelecimentoBean> listaPorCidade = new ArrayList<EstabelecimentoBean>();
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction t = session.beginTransaction();
		Criteria crit = session.createCriteria(EstabelecimentoBean.class);
		crit.add(Restrictions.like("cidade", "%" + cidade + "%"));
		crit.addOrder(Order.asc("nome"));
		listaPorCidade = crit.list();

		return listaPorCidade;

	}
	
	// Pesquisar estabelecimento por Area
	public List<EstabelecimentoBean> pesquisarPorArea(String area)
			throws ClassNotFoundException, SQLException {

		this.connection = con.getConnection();

		List<EstabelecimentoBean> listarArea = new ArrayList<EstabelecimentoBean>();
		String sql = "SELECT * FROM Tbestabelecimento WHERE tipoEstabelecimento_id=?";
		PreparedStatement stmt = connection.prepareStatement(sql);
		stmt.setString(1, area);

		ResultSet rs = stmt.executeQuery();

		while (rs.next()) {
			
			EstabelecimentoBean est = new EstabelecimentoBean();
			est.setId(rs.getInt("idTbEstabelecimento"));
			est.setNome(rs.getString("nome").toUpperCase());
			est.setCnpj(rs.getString("cnpj"));
			est.setCep(rs.getString("cep"));
			est.setRua(rs.getString("rua"));
			est.setNumero(rs.getInt("numero"));
			est.setBairro(rs.getString("bairro"));
			est.setCidade(rs.getString("cidade"));
			est.setUf(rs.getString("uf"));
			est.setResidencial(rs.getString("residencial"));
			est.setCelular(rs.getString("celular"));
			est.setComercial(rs.getString("comercial"));
			est.setContato(rs.getString("contato"));
			est.setEmail(rs.getString("email"));
			est.setWebsite(rs.getString("website"));
			est.setData(rs.getString("data"));
			est.setValor(rs.getDouble("valor"));
			est.setObs(rs.getString("observacao"));
			listarArea.add(est);
		}

		return listarArea;

	}

	public List<EstabelecimentoBean> listaEstabelecimentos()
			throws ClassNotFoundException {
		new Conexao();
		this.connection = con.getConnection();

		List<EstabelecimentoBean> lista = new ArrayList<EstabelecimentoBean>();
		

		String sql = "SELECT * FROM Tbestabelecimento";

		try {
			PreparedStatement stmt = connection.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				
				EstabelecimentoBean estBean = new EstabelecimentoBean();
				estBean.setId(rs.getInt("idTbEstabelecimento"));
				estBean.setNome(rs.getString("nome"));
				estBean.setArea(rs.getString("area"));
				estBean.setCnpj(rs.getString("cnpj"));
				estBean.setCep(rs.getString("cep"));
				estBean.setRua(rs.getString("rua"));
				estBean.setNumero(rs.getInt("numero"));
				estBean.setBairro(rs.getString("bairro"));
				estBean.setCidade(rs.getString("cidade"));
				estBean.setUf(rs.getString("uf"));
				estBean.setResidencial(rs.getString("residencial"));
				estBean.setCelular(rs.getString("celular"));
				estBean.setComercial(rs.getString("comercial"));
				estBean.setContato(rs.getString("contato"));
				estBean.setEmail(rs.getString("email"));
				estBean.setWebsite(rs.getString("website"));
				estBean.setData(rs.getString("data"));
				estBean.setValor(rs.getDouble("valor"));
				estBean.setObs(rs.getString("observacao"));
				estBean.setIsAtivo(rs.getString("isAtivo"));
				estBean.setObsInativo(rs.getString("obsInativo"));
				lista.add(estBean);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return lista;
	}

}
